import pandas as pd
import pymysql


class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='root',
            port=3306,
            database='scenic',
            charset='utf8'
        ) 

    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        select order_id, age, user_count, id_no, user_name, phone from (select o.id as order_id, u.id_no, u.phone, u.user_name,
        case
            when length(u.id_no) = 18 then year(now() - cast(substr(u.id_no, 7 ,4) as signed))
            else null 
            ebd as afe,
            select count(*) from ticket_rder_user_rel where order_id = o.id) as user_count
        from ticket wgere use_counr = 1 and (age<18 or age >= 60)

        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)



if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()